package com.py.credit.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.py.credit.common.ServiceException;
import com.py.credit.dao.EnterpriseFamilyDao;
import com.py.credit.entity.Column;
import com.py.credit.entity.SysUser;
import com.py.credit.service.EnterpriseFamilyService;
import com.py.credit.utils.SQLBuilder;
import com.py.credit.utils.UuidUtil;
import com.py.framework.core.common.Page;

@Service
public class EnterpriseFamilyServiceImpl implements EnterpriseFamilyService {

	@Autowired
	private EnterpriseFamilyDao enterpriseFamilyDao;

	@Override
	public Map getBaseIngfo(String A000001, String A000002) {
		// TODO Auto-generated method stub
		return enterpriseFamilyDao.getBaseIngfo(A000001, A000002);
	}

	@Override
	public List<Map> getLicense(String A000001, String A000002) {
		// TODO Auto-generated method stub
		return enterpriseFamilyDao.getLicense(A000001, A000002);
	}

	@Override
	public List<Map> getPunish(String A000001, String A000002) {
		// TODO Auto-generated method stub
		return enterpriseFamilyDao.getPunish(A000001, A000002);
	}

	@Override
	public List<String> getHighManager(String A000001, String A000002) {
		// TODO Auto-generated method stub
		return enterpriseFamilyDao.getHighManager(A000001, A000002);
	}

	@Override
	public List<String> getBranch(String A000001, String A000002) {
		// TODO Auto-generated method stub
		return enterpriseFamilyDao.getBranch(A000001, A000002);
	}

	@Override
	public List<String> getHolder(String A000001, String A000002) {
		// TODO Auto-generated method stub
		return enterpriseFamilyDao.getHolder(A000001, A000002);
	}

	@Override
	public Map getChart(String A000001, String A000002) {
		Map m = getBaseIngfo(A000001, A000002);
		if (m == null) {
			return null;
		}
		String zczb = (String) m.get("zczb");
		String fr = (String) m.get("fddbr");
		String clrq = (String) m.get("clrq");
		String qyzt = (String) m.get("djzt");
		List<String> holdersString = enterpriseFamilyDao.getHolder(A000001, A000002);
		List<String> branchesString = enterpriseFamilyDao.getBranch(A000001, A000002);
		List<String> highManagersString = enterpriseFamilyDao.getHighManager(A000001, A000002);
		List<String> enterInvestString = enterpriseFamilyDao.getEnterInvest(A000001, A000002);
		List<String> frInvestString = enterpriseFamilyDao.getFrInvest(A000001, A000002);
		List<Map> holders = null;
		List<Map> branches = null;
		List<Map> highManagers = null;
		List<Map> enterInvests = null;
		List<Map> frInvests = null;
		if (holdersString != null && holdersString.size() > 0) {
			holders = new ArrayList<Map>();
			for (int i = 0; i < holdersString.size(); i++) {
				Map a = new HashMap();
				a.put("name", holdersString.get(i));
				holders.add(a);
			}
		}
		if (branchesString != null && branchesString.size() > 0) {
			branches = new ArrayList<Map>();
			for (int i = 0; i < branchesString.size(); i++) {
				Map a = new HashMap();
				a.put("name", branchesString.get(i));
				branches.add(a);
			}
		}
		if (highManagersString != null && highManagersString.size() > 0) {
			highManagers = new ArrayList<Map>();
			for (int i = 0; i < highManagersString.size(); i++) {
				Map a = new HashMap();
				a.put("name", highManagersString.get(i));
				highManagers.add(a);
			}
		}
		if (enterInvestString != null && enterInvestString.size() > 0) {
			enterInvests = new ArrayList<Map>();
			for (int i = 0; i < enterInvestString.size(); i++) {
				Map a = new HashMap();
				a.put("name", enterInvestString.get(i));
				enterInvests.add(a);
			}
		}
		if (frInvestString != null && frInvestString.size() > 0) {
			frInvests = new ArrayList<Map>();
			for (int i = 0; i < frInvestString.size(); i++) {
				Map a = new HashMap();
				a.put("name", frInvestString.get(i));
				frInvests.add(a);
			}
		}
		Map mapAll = new HashMap();
		mapAll.put("zczb", zczb);
		mapAll.put("fr", fr);
		mapAll.put("clrq", clrq);
		mapAll.put("qyzt", qyzt);
		mapAll.put("holders", holders);
		mapAll.put("branches", branches);
		mapAll.put("highManagers", highManagers);
		mapAll.put("enterInvests", enterInvests);
		mapAll.put("frInvests", frInvests);
		return mapAll;
	}

	@Override
	public List<Map> selectByConditionForPage(List<String> columns, String[] additionalColumns, String conditionStr,
			Page page, String mainType) {
		List<String> conditions = SQLBuilder.createConditionSql(conditionStr);
		List<Map> batchLogs = enterpriseFamilyDao.selectByConditionForPage(columns, additionalColumns, conditions, page,
				mainType);

		return batchLogs;
	}

	@Override
	public List<Map> selectAll(String mainType, List<String> listSelected, String batchId) {
		// TODO Auto-generated method stub
		if (StringUtils.isBlank(batchId)) {
			return enterpriseFamilyDao.selectAll(mainType, listSelected);
		} else {
			return enterpriseFamilyDao.selectBatchExportForAll(mainType, batchId);
		}

	}

	@Override
	public List<Map> autoScoreByIndexSql(String mainType, String sql, List<String> listSelected, String batchId) {
		// TODO Auto-generated method stub
		if (StringUtils.isNotBlank(batchId)) {
			return enterpriseFamilyDao.autoScoreByIndexSqlAndImportFile(mainType, sql, batchId);
		} else {
			return enterpriseFamilyDao.autoScoreByIndexSql(mainType, sql, listSelected);
		}

	}

	/**
	 * 上传文件
	 */

	@Override

	public String doUploadFile(MultipartFile file, SysUser userInfo, String absolutepath, String mainType)
			throws ServiceException, Exception {

		String batchId = UuidUtil.getInstance().getUUid();
		// 文件存储路径
		StringBuilder pathFile = new StringBuilder(200);
		pathFile.append(File.separator);
		pathFile.append(userInfo.getId());
		pathFile.append(File.separator);
		pathFile.append(batchId);
		// 获取文件的后缀
		String fileType = null;
		if (file != null) {
			// 获取文件后缀
			if (file.getOriginalFilename().contains(".")) {
				fileType = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."),
						file.getOriginalFilename().length());
			}
		} else {
			throw new ServiceException("上传的文件不得为空");// 抛出异常
		}
		if (!".xls".equalsIgnoreCase(fileType) && !".xlsx".equalsIgnoreCase(fileType)) {
			throw new ServiceException("上传的文件格式不支持。");
		}
		// 先存储文件
		fileUpload(file, absolutepath + "/upload" + pathFile.toString(), batchId, fileType);

		Sheet st = readExcelFileForSheet(
				absolutepath + "/upload" + pathFile.toString() + File.separator + batchId + fileType);

		List<Column> columns = new ArrayList<Column>();
		if ("100000".equals(mainType)) {
			Column c1 = new Column();
			c1.setMetricName("企业名称");
			Column c2 = new Column();
			c2.setMetricName("统一社会信用代码");
			columns.add(c1);
			columns.add(c2);
		} else if ("1000001".equals(mainType)) {
			Column c1 = new Column();
			c1.setMetricName("姓名");
			Column c2 = new Column();
			c2.setMetricName("证件号");
			columns.add(c1);
			columns.add(c2);
		} else if ("1000002".equals(mainType)) {
			Column c1 = new Column();
			c1.setMetricName("非企业社会团体名称");
			Column c2 = new Column();
			c2.setMetricName("统一社会信用代码");
			columns.add(c1);
			columns.add(c2);
		}
		Map<String, Object> map = readExcelFileHead(st, columns);
		if (map == null || map.isEmpty()) {
			readExcelData(st, batchId, mainType);
			return batchId;
			// listTybms=enterpriseFamilyDao.selectBatchExport("100000",batchId);
		} else {
			throw new RuntimeException((String) map.get("ERROR_MESS"));
		}

	}

	/**
	 * 上传文件
	 * 
	 * @param file
	 *            文件
	 * @param request
	 * @param orgBm
	 *            所属编码
	 * @param fileName
	 *            文件名称
	 * @param tableNameEn
	 *            文件英文名
	 * @throws IOException
	 * @throws IllegalStateException
	 */
	private void fileUpload(MultipartFile file, String pathFile, String batchId, String fileType)
			throws IllegalStateException, IOException {
		File uploadFile = null;

		File dirPath = new File(pathFile);
		uploadFile = new File(pathFile + File.separator + batchId + fileType);
		if (!dirPath.exists()) {
			uploadFile.mkdirs();
		}
		file.transferTo(uploadFile);

	}

	public Sheet readExcelFileForSheet(String filePath) throws IOException {
		Workbook wb = null;
		FileInputStream fs = new FileInputStream(filePath);
		// Biff8EncryptionKey.setCurrentUserPassword("AAABBBABABA{");

		if (filePath.endsWith(".xls")) {
			wb = new HSSFWorkbook(fs);
		}
		if (filePath.endsWith(".xlsx")) {
			wb = new XSSFWorkbook(fs);
		}

		Sheet st = wb.getSheetAt(0);

		fs.close();

		return st;
	}

	public Map<String, Object> readExcelFileHead(Sheet st, List<Column> columns) {
		Map<String, Object> map = null;
		try {
			Row row = null;
			// 获取校验头
			row = st.getRow(0);
			for (int i = 0; i < columns.size(); i++) {

				if (!row.getCell(i).toString().trim().equals(columns.get(i).getMetricName())) {
					/*
					 * LOG.debug("EXCEL第{}列表头:{}", i, row.getCell(i));
					 * LOG.debug("数据库配置 第{}列表头:{}", i,
					 * columns.get(i).getMetricName());
					 * LOG.info("模板表头已被更改,请用规定的模板上传数据。");
					 */
					map = new HashMap<String, Object>();
					map.put("ERROR_MESS", "模板表头已被更改,请用规定的模板上传数据。");
					break;
				}

			}
			return map;

		} catch (EncryptedDocumentException e) {
			map = new HashMap<String, Object>();
			map.put("ERROR_MESS", "模板保护密码已被更改");

		} catch (Exception e) {
			map = new HashMap<String, Object>();
			map.put("ERROR_MESS", "模板表头已被更改,请用规定的模板上传数据。");
		}
		return map;
	}

	private void readExcelData(Sheet st, String batchId, String mainType) throws IOException {
		int start = 0;
		int end = 0;
		List<Map<String, Object>> listDataMap = readExcelData(start, 5000, st);
		if (listDataMap != null && listDataMap.size() > 0) {
			enterpriseFamilyDao.insertBatchExport(listDataMap, batchId);
			enterpriseFamilyDao.updateBatchExportFalse(batchId, mainType);
			enterpriseFamilyDao.updateBatchExportTrue(batchId, mainType);
		} else {
			throw new RuntimeException("没有匹配到企业数据！");
		}
	}

	public List<Map<String, Object>> readExcelData(int start, int end, Sheet st) {
		// 第一,二行为标题，不取
		Row row = null;
		int length = 0;
		Map<String, Object> map = null;
		Cell cell = null;
		String value = null;

		String[] cloumn = new String[] { "qymc", "tyshxydm" };
		length = st.getLastRowNum();

		if (end > length) {
			end = length;
		}
		List<Map<String, Object>> listDataMap = new LinkedList<>();
		for (int rowIndex = start + 1; rowIndex <= end; rowIndex++) {
			row = st.getRow(rowIndex);
			if (row == null) {
				continue;
			}

			short lastCellNum = row.getLastCellNum();

			boolean emptyRow = true;

			for (int cellIndex = 0; cellIndex <= lastCellNum; cellIndex++) {

				if (row.getCell(cellIndex, Row.RETURN_BLANK_AS_NULL) != null) {

					row.getCell(cellIndex).setCellType(HSSFCell.CELL_TYPE_STRING);

					if (!"".equals(row.getCell(cellIndex).getStringCellValue().trim())) {
						{

							emptyRow = false;
							break;
						}
					}

				}
			}

			if (emptyRow) {

				continue;
			}
			map = new HashMap<String, Object>();
			for (int i = 0; i < lastCellNum; i++) {
				value = "";
				cell = row.getCell(i);
				if (cell != null) {
					// 注意：一定要设成这个，否则可能会出现乱码
					switch (cell.getCellType()) {
					case HSSFCell.CELL_TYPE_STRING:
						value = cell.getStringCellValue().trim();
						break;
					case HSSFCell.CELL_TYPE_BLANK:
						break;

					default:
						value = "";
					}
				}
				map.put(cloumn[i], value);
				map.put("rowIndex", rowIndex + 1);
			}
			/* 把数据添加到List里面 */
			listDataMap.add(map);
		}

		return listDataMap;
	}

	@Override
	public List<Map<String, Object>> selectBatchExportForPage(List<String> columns, String[] additionalColumns,
			String conditionStr, Page page, String batchId, String mainType) {
		List<String> conditions = SQLBuilder.createConditionSql(conditionStr);
		List<Map<String, Object>> batchLogs = enterpriseFamilyDao.selectBatchExportForPage(columns, additionalColumns,
				conditions, page, batchId, mainType);

		return batchLogs;
	}

	@Override
	public int selectNoRepeatCount(String mainType, String batchId) {
		// TODO Auto-generated method stub
		return enterpriseFamilyDao.selectNoRepeatCount(mainType, batchId);
	}

	@Override
	public int selectRightAllCount(String mainType, String batchId) {
		// TODO Auto-generated method stub
		return enterpriseFamilyDao.selectRightAllCount(mainType, batchId);
	}

	@Override
	public int selectErrorCount(String mainType, String batchId) {
		// TODO Auto-generated method stub
		return enterpriseFamilyDao.selectErrorCount(mainType, batchId);
	}

	@Override
	public List<String> selectBatchExport(String mainType, String batchId) {
		// TODO Auto-generated method stub
		return enterpriseFamilyDao.selectBatchExport(mainType, batchId);
	}

	@Override
	public List<String> selectTybmByLimit(String mainType, int start, int size) {
		// TODO Auto-generated method stub
		return enterpriseFamilyDao.selectTybmByLimit(mainType, start, size);
	}

	@Override
	public Long selectCountAll(String mainType) {
		// TODO Auto-generated method stub
		return enterpriseFamilyDao.selectCountAll(mainType);
	}

	@Override
	public List<String> getEnterInvest(String A000001, String A000002) {
		// TODO Auto-generated method stub
		return enterpriseFamilyDao.getEnterInvest(A000001,A000002);
	}

	@Override
	public List<String> getFrInvest(String A000001, String A000002) {
		// TODO Auto-generated method stub
		return enterpriseFamilyDao.getFrInvest(A000001,A000002);
	}

}
